﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AppConfigurationMVC.Models;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace AppConfiguration.DataAccess
{
    public class DataHelper
    {
        public void CreateConfig(ConfigurationModels config)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConfigConnStr"].ConnectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "insert into tblConfig (config_key,config_value,config_reason,config_enabled) values ('" + config.ConfigKey + "','" + config.ConfigValue + "','" + config.ConfigReason + "'," + Convert.ToInt16(config.ConfigEnabled) + ")";

                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public List<ConfigurationModels> GetConfigs()
        {
            List<ConfigurationModels> cnfg = new List<ConfigurationModels>();
            DataSet ds = new DataSet();

            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConfigConnStr"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "select config_key,config_value,config_reason,config_enabled,id from tblConfig";

                SqlDataAdapter da = new SqlDataAdapter(cmd);
                
                da.Fill(ds);
            }

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                ConfigurationModels c = new ConfigurationModels();
                c.ConfigKey = ds.Tables[0].Rows[i].ItemArray[0].ToString();
                c.ConfigValue = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                c.ConfigReason = ds.Tables[0].Rows[i].ItemArray[2].ToString();
                c.RecordCreateTms = DateTime.Now;
                c.RecordUpdateTms = DateTime.Now;
                c.ConfigEnabled = Convert.ToBoolean(ds.Tables[0].Rows[i].ItemArray[3].ToString());
                c.ConfigID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[4].ToString());
                cnfg.Add(c);
            }

            return cnfg;
        }

        public void UpdateConfig(ConfigurationModels config)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConfigConnStr"].ConnectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "update tblConfig set config_value='" + config.ConfigValue + "',config_reason='" + config.ConfigReason + "',config_enabled=" + Convert.ToInt16(config.ConfigEnabled) + " where id=" + config.ConfigID;

                cmd.ExecuteNonQuery();
                con.Close();
            }
        }

        public void DeleteConfig(int id)
        {
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConfigConnStr"].ConnectionString))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "delete from tblConfig where id=" + id;

                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}
